﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using bdxyit.com.RecruitmentAppSys.IDAL;
using bdxyit.com.RecruitmentAppSys.Models;
using System.Data.SqlClient;
using bdxyit.com.RecruitmentAppSys.DBUtility;
using System.Data;
/***
*机器名：BDXYIT-HUI
*项目名称：$projectname$
*命名空间：bdxyit.com.RecruitmentAppSys.SQLDAL
*文件名：CompanyInfoModels
*系统时间：3/25/2012 3:33:10 PM
*当前用户：huihui
*创建时间：2012
*
*修改时间：2012 3/25/2012 3:33:10 PM
*修改事件：
***/

namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class CompanyInfoDAL : ICompanyInfoDAL
    {
        private const string SQL_SELECT_COMPANYINFO = " select  CompanyID,CompanyName,CompanyType,FCompanyID,Location,Other,Contact from CompanyInfos ";

        private const string SQL_SELECT_COMPANYINFO_BY_COMPANYID = " select  top 1 CompanyID,CompanyName,CompanyType,FCompanyID,Location,Other,Contact from CompanyInfos "
                                                                + "  where CompanyID=@CompanyID";
        private const string SQL_DELETE_COMPANYINFO_BY_COMPANYID = " delete   from CompanyInfos "
                                                                + "  where CompanyID=@CompanyID";

        private const string SQL_UPDATE_COMPANYINFO = "update CompanyInfoes set CompanyID=@CompanyID,CompanyName=@CompanyName,CompanyType=@CompanyType,FCompanyID=@FCompanyID,Location=@Location,Other=@Other,Contact=@Contact where CompanyID=@CompanyID";

        private const string SQL_INSERT_COMPANYINFO = " insert into CompanyInfos("
                                                        + " CompanyID,CompanyName,CompanyType,FCompanyID,Location,Other,Contact)"
                                                        + "  values ("
                                                        + " @CompanyID,@CompanyName,@CompanyType)";
        private const string FIELD_COMPANYID = "@CompanyID";
        private const string FIELD_COMPANYNAME = "@CompanyName";
        private const string FIELD_COMPANYTYPE = "@CompanyType";
        private const string FIELD_FCompanyID = "@FCompanyID";
        private const string FIELD_Location = "@Location";
        private const string FIELD_Other = "@Other";
        private const string FIELD_Contact = "@Contact";
        public List<CompanyInfo> SelectCompanyInfo()
        {
            List<CompanyInfo> ci = new List<CompanyInfo>();
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_COMPANYINFO))
            {
                while (rdr.Read())
                {
                    CompanyInfo c = new CompanyInfo();
                    if (rdr["CompanyID"] != null && rdr["CompanyID"].ToString() != "")
                    {
                        c.CompanyID = rdr["CompanyID"].ToString();
                    }
                    if (rdr["CompanyName"] != null && rdr["CompanyName"].ToString() != "")
                    {
                        c.CompanyName = rdr["CompanyName"].ToString();
                    }
                    if (rdr["CompanyType"] != null && rdr["CompanyType"].ToString() != "")
                    {
                        c.CompanyType = rdr["CompanyType"].ToString();
                    }
                    if (rdr["FCompanyID"] != null && rdr["FCompanyID"].ToString() != "")
                    {
                        c.FCompanyID = rdr["FCompanyID"].ToString();
                    }
                    if (rdr["Location"] != null && rdr["Location"].ToString() != "")
                    {
                        c.Location = rdr["Location"].ToString();
                    }
                    if (rdr["Other"] != null && rdr["Other"].ToString() != "")
                    {
                        c.Other = rdr["Other"].ToString();
                    } if (rdr["Contact"] != null && rdr["Contact"].ToString() != "")
                    {
                        c.Contact = rdr["Contact"].ToString();
                    }
                    ci.Add(c);
                }
            }
            return ci;
        }

        public CompanyInfo GetCompanyInfoByCompanyID(string companyID)
        {
            CompanyInfo company = null;
            bool flag = false;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_COMPANYID, SqlDbType.VarChar, 18);
            parm.Value = companyID;
            SqlCommand cmd = new SqlCommand();
            //执行读取家庭成员
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_COMPANYINFO, parm))
            {
                if (rdr.Read())
                {
                    company = new CompanyInfo();
                    CompanyInfo c = new CompanyInfo();
                    if (rdr["CompanyID"] != null && rdr["CompanyID"].ToString() != "")
                    {
                        company.CompanyID = rdr["CompanyID"].ToString();
                    }
                    if (rdr["CompanyName"] != null && rdr["CompanyName"].ToString() != "")
                    {
                        company.CompanyName = rdr["CompanyName"].ToString();
                    }
                    if (rdr["CompanyType"] != null && rdr["CompanyType"].ToString() != "")
                    {
                        company.CompanyType = rdr["CompanyType"].ToString();
                    }
                    if (rdr["FCompanyID"] != null && rdr["FCompanyID"].ToString() != "")
                    {
                        c.FCompanyID = rdr["FCompanyID"].ToString();
                    }
                    if (rdr["Location"] != null && rdr["Location"].ToString() != "")
                    {
                        c.Location = rdr["Location"].ToString();
                    }
                    if (rdr["Other"] != null && rdr["Other"].ToString() != "")
                    {
                        c.Other = rdr["Other"].ToString();
                    } if (rdr["Contact"] != null && rdr["Contact"].ToString() != "")
                    {
                        c.Contact = rdr["Contact"].ToString();
                    }
                }
            }
            return company;
        }
        /// <summary>
        /// 移除一个招聘单位
        /// </summary>
        /// <param name="companyID"></param>
        /// <returns></returns>
        public bool DeleteCompanyInfoByUserId(string companyID)
        {
            bool flag = false;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_COMPANYID, SqlDbType.VarChar, 18);
            parm.Value = companyID;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_COMPANYINFO_BY_COMPANYID;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        /// <summary>
        /// 插入一个招聘单位
        /// </summary>
        /// <param name="company"></param>
        /// <returns></returns>
        public bool InsertCompanyInfo(CompanyInfo company)
        {
            bool flag = false;
            SqlParameter[] CompanyParameters = GetCompanyParameters();
            SqlCommand cmd = new SqlCommand();
            CompanyParameters[0].Value = company.CompanyID;
            CompanyParameters[1].Value = company.CompanyName;
            CompanyParameters[2].Value = company.CompanyType;
            CompanyParameters[3].Value = company.FCompanyID;
            CompanyParameters[4].Value = company.Location;
            CompanyParameters[5].Value = company.Other;
            CompanyParameters[6].Value = company.Contact;
            foreach (SqlParameter parm in CompanyParameters)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_COMPANYINFO;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;

        }
        /// <summary>
        /// 更新一个招聘单位
        /// </summary>
        /// <param name="company"></param>
        /// <returns></returns>
        public bool UpdateCompanyInfo(CompanyInfo company)
        {
            bool flag = false;
            SqlParameter[] CompanyParameters = GetCompanyParameters();
            SqlCommand cmd = new SqlCommand();
            CompanyParameters[0].Value = company.CompanyID;
            CompanyParameters[1].Value = company.CompanyName;
            CompanyParameters[2].Value = company.CompanyType;
            CompanyParameters[3].Value = company.FCompanyID;
            CompanyParameters[4].Value = company.Location;
            CompanyParameters[5].Value = company.Other;
            CompanyParameters[6].Value = company.Contact;
            foreach (SqlParameter parm in CompanyParameters)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_COMPANYINFO;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <returns></returns>
        private SqlParameter[] GetCompanyParameters()
        {
            SqlParameter[] parms = new SqlParameter[] {
					new SqlParameter(FIELD_COMPANYID, SqlDbType.VarChar, 18),
					new SqlParameter(FIELD_COMPANYNAME, SqlDbType.VarChar, 50),
					new SqlParameter(FIELD_COMPANYTYPE, SqlDbType.VarChar, 50),
                    new SqlParameter(FIELD_FCompanyID, SqlDbType.VarChar, 50),
					new SqlParameter(FIELD_Location, SqlDbType.VarChar, 50),
                    new SqlParameter(FIELD_Other, SqlDbType.VarChar, 50),
                     new SqlParameter(FIELD_Contact, SqlDbType.VarChar, 50)
				
            };
            return parms;
        }
    }
}